/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Hostel;
import java.sql.*;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {
    static Connection con=null;
    
    public static int addHostel(HostelBean hb) throws SQLException{
        int i=0;
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("insert into HOSTEL values(HOSTELID.nextval,?,?,?,?,?,?,sysdate)");
            pre.setString(1, hb.getHostelName());
            pre.setString(2, hb.getHostelType());
            pre.setString(3, hb.getHostelAddress());
            pre.setInt(4, hb.getWardenId());
            pre.setString(5, hb.getMobile());
            pre.setString(6, hb.getPhone());
            i=pre.executeUpdate();
            
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return i;
    }
    public static int editHostel(HostelBean hb) throws SQLException{
        int i=0;
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("update HOSTEL set  HOSTEL_NAME=?, HOSTEL_TYPE=?, HOSTEL_ADDRESS=?, WARDEN_NAME=?, MOBILE=?, PHONE=?, ENTRY_DATE=sysdate where HOSTEL_ID=?");
            pre.setString(1, hb.getHostelName());
            pre.setString(2, hb.getHostelType());
            pre.setString(3, hb.getHostelAddress());
            pre.setInt(4, hb.getWardenId());
            pre.setString(5, hb.getMobile());
            pre.setString(6, hb.getPhone());
            pre.setInt(7, hb.getHotelId());
            i=pre.executeUpdate();
            
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return i;
    }
    
    public static Vector ViewAll() throws SQLException{
        Vector<Object> list=new Vector<Object>();
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select * from hostel");
            ResultSet rs=pre.executeQuery();
            while(rs.next()){
            HostelBean hb=new HostelBean();
            hb.setHotelId(rs.getInt(1));
            hb.setHostelName(rs.getString(2));
            hb.setHostelType(rs.getString(3));
            hb.setHostelAddress(rs.getString(4));
            hb.setWardenId(rs.getInt(5));
            hb.setMobile(rs.getString(6));
            hb.setPhone(rs.getString(7));
            hb.setEntryDate(rs.getDate(8));
            list.add(hb);
            }
            
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return list;
    }
    public static HostelBean ViewSpecific(String cname, String val) throws SQLException{
        HostelBean hb=new HostelBean();
        try{
            
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select * from hostel where " + cname + "=?");
            ResultSet rs=pre.executeQuery();
            while(rs.next()){
            hb.setHotelId(rs.getInt(1));
            hb.setHostelName(rs.getString(2));
            hb.setHostelType(rs.getString(3));
            hb.setHostelAddress(rs.getString(4));
            hb.setWardenId(rs.getInt(5));
            hb.setMobile(rs.getString(6));
            hb.setPhone(rs.getString(7));
            hb.setEntryDate(rs.getDate(8));
            }
            
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return hb;
    }
 public static int delete(int id) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("delete from hostel where hostel_id=?");
            pre.setInt(1, id);
            i = pre.executeUpdate();
        } catch (Exception e) {
            return i;
        } finally {
            con.close();
        }
        return i;
    }
}
